package com.matt.repository.impl;

import com.dotflat.util.AssertUtil;
import com.matt.domain.nursery.NurserySchedule;
import com.matt.foundation.constant.ClassesConstant;
import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.vo.nursery.NurseryClassesShortVo;
import com.matt.foundation.vo.nursery.NurseryClassesVo;
import com.matt.foundation.vo.nursery.NurseryScheduleV2Vo;
import com.matt.foundation.vo.nursery.NurseryScheduleVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.NurseryScheduleNativeRepository;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by lch.
 * form com.matt
 * on 2018/11/26
 * *******************************
 */
@Service
public class NurseryScheduleNativeRepositoryImpl extends BaseNativeSqlRepository implements NurseryScheduleNativeRepository {


    @Override
    public NurseryScheduleVo findById(String nurseryScheduleId) {
        String querySql = String.format("select cl.id as classesId,cl.name as classesName,ns.id as id,ns.status as status,ns.lessonDate as lessonDate,ns.courseType as courseType " +
                " from classes as cl inner join nursery_schedule as ns on cl.id=ns.classesId where ns.id='%s'",nurseryScheduleId);
        List<NurseryScheduleVo> list = sqlObjectList(querySql,NurseryScheduleVo.class);
        if (list.size() > 0)
            return list.get(0) == null ? null : list.get(0);
        return null;
    }

    @Override
    public List<NurseryClassesShortVo> getNurseryClassesShortVo(Date beginDate,Date endDate,String areaId, Integer category, int delete) {
        String querySql = String.format("SELECT cl.id as classesId,cl.`name` as classesName,cl.category as category, cpc.courseId as coursePlanId,cpc.courseDate as courseDate " +
                " FROM classes as cl INNER JOIN course_plan_classes as cpc ON cl.id=cpc.classesId  " +
                "WHERE cpc.courseId is not NULL and cl.category='%s' and cl.areaId='%s' and cl.deleted='%s' " +
                " and date(cpc.courseDate) >='%s' and date(cpc.courseDate)<='%s' ",category,areaId,delete,DateUtil.convertStr(beginDate),DateUtil.convertStr(endDate));
        List<NurseryClassesShortVo> list = sqlObjectList(querySql,NurseryClassesShortVo.class);
        return list;
    }

    @Override
    public List<NurserySchedule> findScheduleBylessonDate(String beginDate, String endDate, String areaId) {
        String querySql = String.format("select ns.id as id,ns.created as created,ns.deleted as deleted,ns.classesId as classesId,ns.status as status,ns.lessonDate as lessonDate " +
                " from nursery_schedule as ns inner join classes as cl on cl.id = ns.classesId where cl.areaId='%s' and cl.category=1 and date(ns.lessonDate) >='%s' and date(ns.lessonDate) <='%s'",areaId,beginDate,endDate);
        List<NurserySchedule> list = sqlObjectList(querySql,NurserySchedule.class);
        return list;
    }

    @Override
    public List<NurserySchedule> findScheduleClassIdByLessonDate(String beginDate, String endDate, String classId) {
        String querySql = String.format("select ns.id as id,ns.created as created,ns.deleted as deleted,ns.classesId as classesId,ns.status as status,ns.lessonDate as lessonDate " +
                " from nursery_schedule as ns inner join classes as cl on cl.id = ns.classesId where   cl.id ='%s' and cl.category=1 and date(ns.lessonDate) >='%s' and date(ns.lessonDate) <='%s'",classId,beginDate,endDate);
        List<NurserySchedule> list = sqlObjectList(querySql,NurserySchedule.class);
        return list;
    }


    @Override
    public List<NurserySchedule> findScheduleByChildrenIdAndLessonDate(String childrenId,String beginDate, String endDate) {
        String querySql = String.format("select ns.id as id,ns.created as created,ns.deleted as deleted,ns.status as status,ns.lessonDate as lessonDate, ns.childrenId , ns.teacherId  " +
                " from nursery_schedule ns  where  ns.childrenId = '%s' and  date(ns.lessonDate) >='%s' and date(ns.lessonDate) <='%s'",childrenId,beginDate,endDate);
        List<NurserySchedule> list = sqlObjectList(querySql,NurserySchedule.class);
        return list;
    }
    @Override
    public List<NurseryClassesVo> findByStaffId(String staffId) {
        String querySql = String.format("select cl.id as classesId,cl.name as classesName " +
                " from classes as cl inner join rel_classes_teacher as rct on cl.id=rct.classesId " +
                " where cl.category='%s' and cl.deleted=0 and rct.staffId='%s'", ClassesConstant.Category.NURSERY.codeOf(),staffId);
        List<NurseryClassesVo> list = sqlObjectList(querySql,NurseryClassesVo.class);
        return list;
    }

    public List<NurseryClassesVo> findByAreaIdAndStaffId(String areaId, String staffId){
        String querySql = String.format("select cl.id as classesId,cl.name as classesName " +
                " from classes as cl inner join rel_classes_teacher as rct on cl.id=rct.classesId " +
                " where cl.category='%s' and cl.areaId='%s' and cl.deleted=0 and rct.staffId='%s' group by classesId",  ClassesConstant.Category.NURSERY.codeOf(), areaId, staffId);
        List<NurseryClassesVo> list = sqlObjectList(querySql,NurseryClassesVo.class);
        return list;
    }
    
    public List<NurseryClassesVo> findByAreaId(String areaId){
        String querySql = String.format("select cl.id as classesId,cl.name as classesName " +
                " from classes as cl inner join rel_classes_teacher as rct on cl.id=rct.classesId " +
                " where cl.category='%s' and cl.areaId='%s' and cl.deleted=0 group by classesId",  ClassesConstant.Category.NURSERY.codeOf(), areaId);
        List<NurseryClassesVo> list = sqlObjectList(querySql,NurseryClassesVo.class);
        return list;
    }

    @Override
    public NurseryScheduleVo findByClassesIdAndLessonDate(String classesId, String date) {
        String querySql = String.format("select cl.id as classesId,cl.name as classesName,ns.id as id,ns.status as status,ns.lessonDate as lessonDate,ns.courseType as courseType " +
                " from classes as cl inner join nursery_schedule as ns on cl.id=ns.classesId where ns.classesId='%s' and date(ns.lessonDate)='%s'", classesId,date);
        List<NurseryScheduleVo> list = sqlObjectList(querySql,NurseryScheduleVo.class);
        if (list.size() > 0)
            return list.get(0) == null ? null : list.get(0);
        return null;
    }

    @Override
    public NurseryScheduleVo findByTeacherIdAndChildrenIdAndLessonDate(String teacherId, String childrenId, String date){
        String querySql = String.format(
                "select cl.id as classesId,cl.name as classesName,ns.id as id,ns.status as status,ns.lessonDate as lessonDate,ns.courseType as courseType " +
                " from classes as cl inner join nursery_schedule as ns on cl.id=ns.classesId " +
                " where ns.teacherId='%s' and childrenId='%s' and date(ns.lessonDate)='%s'", teacherId, childrenId, date);
        List<NurseryScheduleVo> list = sqlObjectList(querySql,NurseryScheduleVo.class);

        if (list.size() > 0)
            return list.get(0) == null ? null : list.get(0);

        return null;
    }

    @Override
    public List<NurseryScheduleVo> findByClassesIdAndWeekTime(String classesId,  String weekStartDay,String weekEndDay) {
        String querySql = String.format("select cl.id as classesId,cl.name as classesName,ns.id as id,ns.status as status,ns.lessonDate as lessonDate,ns.courseType as courseType " +
                " from classes as cl inner join nursery_schedule as ns on cl.id=ns.classesId where ns.classesId='%s' and date(ns.lessonDate) >='%s' and date(ns.lessonDate) <='%s'", classesId, weekStartDay, weekEndDay);
        querySql += " order by ns.lessonDate asc ";
        List<NurseryScheduleVo> list = sqlObjectList(querySql,NurseryScheduleVo.class);
       // if (list.size() > 0)
       // return list.get(0) == null ? null : list.get(0);
        return list;
    }

    @Override
    public int findCountByClassesIdAndTime(String classesId, String beginDate, String endDate) {
        String querySql = String.format("select count(id) from nursery_schedule where classesId='%s' and date(lessonDate) >='%s' and date(lessonDate) <='%s' ",classesId,beginDate,endDate);
        List list= sqlSingleList(querySql);
        if (list.size()>0){
            return list.get(0) !=null ? Integer.parseInt(list.get(0).toString()) : 0;
        }
        return 0;
    }

    @Override
    public List<Object[]> getAreaIdAndClassesName(String nurseryScheduleId) {
        String querySql = String.format("select cl.name as classesName,cl.areaId as areaId,ns.lessonDate as lessonDate,cl.id as classesId from nursery_schedule as ns left join classes as cl on ns.classesId=cl.id where ns.id='%s'",nurseryScheduleId);
        List<Object[]> list = sqlArrayList(querySql);
        return list;
    }

    @Override
    public List<NurseryScheduleV2Vo> findByNurseryScheduleV2Vo(String areaId, String classesId, String teacherId,String childrenName) {
        String querySql = String.format("select cl.id as classesId,cl.name as classesName,ns.id as id,ns.status as status,ns.lessonDate as lessonDate,ns.courseType as courseType \n" +
                ",IFNULL(c.id,'') as childrenId ,IFNULL(c.name,'') as childrenName ,ns.teacherId" +
                "  from classes as cl inner join nursery_schedule as ns on cl.id=ns.classesId  LEFT JOIN children c  ON c.id = ns.childrenId" +
                " where  ns.areaId ='%s' ", areaId );

        if (AssertUtil.isNotEmpty(classesId)){
            querySql += String.format(" and ns.classesId='%s' ", classesId);
        }
        if (AssertUtil.isNotEmpty(childrenName)){
            querySql += String.format(" and c.name like \"%%%s%%\" ", childrenName);
        }
        if (AssertUtil.isNotEmpty(teacherId)){
            querySql += String.format(" and ns.teacherId =  '%s' ", teacherId);
        }
        querySql += " order by ns.lessonDate asc ";
        List<NurseryScheduleV2Vo> list = sqlObjectList(querySql,NurseryScheduleV2Vo.class);
        return list;
    }
}
